package com.wanxi.dao.impl;

import com.wanxi.dao.UserDao;
import com.wanxi.model.UserModel;
import com.wanxi.util.JDBC;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDao {
    public int add(UserModel model) {
//        SimpleDateFormat
//        LocaleDateTime
//        将数据放在数据库
//        JDBC连接数据库,不可能自己去写里面的原始代码，所以直接用工具即可
        String sql = "INSERT INTO `user` (" +
                "`username`, `address`, `create_time`, " +
                "`email`, `enable`, `password`," +
                " `phone`, `sex`, `update_time`, " +
                "`hobby`, `birthday`, `remark`) VALUES (" +
                "'" + model.getName() + "', '" + model.getAddress() + "', now(), " +
                "'" + model.getEmail() + "', '" + model.getEnable() + "', '" + model.getPassword() + "'," +
                " '" + model.getPhone() + "', '" + model.getSex() + "', sysdate(), " +
                "'" + model.getHobby() + "', '" + model.getBirthday() + "', '');";
        return JDBC.update(sql);
    }

    /**
     * 返回true表示数据库里面没有数据
     *
     * @param model
     * @return
     */
    public boolean findByName(UserModel model) {
        String sql = "select * from user where username ='" + model.getName() + "' ";
        ResultSet query = JDBC.query(sql);
        try {
            while (query.next()) {
//            query.getInt("id");
//            query.getString("name");
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public boolean findByNameAndPassword(UserModel userModel) {
        String sql = "select * from user where username='" + userModel.getName() + "' and password='" + userModel.getPassword() + "'";
        ResultSet query = JDBC.query(sql);
        try {
            while (query.next()) {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public int update(UserModel model) {
        String sql = "UPDATE `user` SET " +
                "`username` = '" + model.getName() + "', " +
                "`address` = '" + model.getAddress() + "', " +
                "`email` = '" + model.getEmail() + "', " +
                "`enable` = '" + model.getEnable() + "', " +
                "`password` = '" + model.getPassword() + "'," +
                " `phone` = '" + model.getPhone() + "', " +
                "`sex` = '" + model.getSex() + "', " +
                "`update_time` = now()," +
                " `hobby` = '" + model.getHobby() + "', " +
                "`birthday` = '" + model.getBirthday() + "'," +
                " `remark` = '' WHERE (" +
                "`id` = '" + model.getId() + "');";
        return JDBC.update(sql);
    }

    public int delete(UserModel model) {
        String sql = "DELETE FROM `user` WHERE (`id` = '" + model.getId() + " ');";
        return JDBC.update(sql);
    }

    public UserModel findById(UserModel model) {
        String sql = "select * from user where id=" + model.getId();
        ResultSet query = JDBC.query(sql);
        try {
            while (query.next()) {
                return UserModel.builder()
                        .id(query.getInt("id"))
                        .name(query.getString("username"))
                        .address(query.getString("address"))
                        .birthday(query.getString("birthday"))
                        .hobby(query.getString("hobby"))
                        .phone(query.getString("phone"))
                        .email(query.getString("email"))
                        .sex(query.getString("sex"))
                        .enable(query.getInt("enable"))
                        .build();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public List<UserModel> findAll(UserModel model) {
        String sql = "select id,username,address,create_time,email,enable,phone,sex,update_time,hobby,date_format(birthday,'%Y-%m-%d') birthday from user where  true";
        sql += getWhereSql(model);
        sql += " order by id desc ";
        sql += " limit " + (model.getPage() - 1) * model.getLimit() + "," + model.getLimit() + "";
        ResultSet query = JDBC.query(sql);
        List<UserModel> list = new ArrayList<>();
        try {
            while (query.next()) {
                list.add(UserModel.builder()
                        .id(query.getInt("id"))
                        .name(query.getString("username"))
                        .address(query.getString("address"))
                        .birthday(query.getString("birthday"))
                        .hobby(query.getString("hobby"))
                        .phone(query.getString("phone"))
                        .email(query.getString("email"))
                        .sex(query.getString("sex"))
                        .enable(query.getInt("enable"))
                        .build());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    private String getWhereSql(UserModel model) {
        String sql = "";
        if (!"".equals(model.getName())) {
            sql += " and username like '%" + model.getName() + "%' ";
        }
        if (!"".equals(model.getSex())) {
            sql += " and sex='" + model.getSex() + "' ";
        }
        if (!"".equals(model.getPhone())) {
            sql += " and phone like '%" + model.getPhone() + "%' ";
        }
        if (model.getEnable() != -1) {
            sql += " and enable ='" + model.getEnable() + "' ";
        }
        if (!"".equals(model.getEmail())) {
            sql += " and email like '%" + model.getEmail() + "%' ";
        }
        if (!"1111-01-01".equals(model.getStartBirthday())) {
            sql += " and birthday >='" + model.getStartBirthday() + "'";
        }
        if (!"1111-01-01".equals(model.getEndBirthday())) {
            sql += " and birthday <='" + model.getEndBirthday() + "'";
        }
        if (!"".equals(model.getHobby())) {
            String[] hobbys = model.getHobby().split(",");
            sql += " and (";
            for (int i = 0; i < hobbys.length; i++) {
                if (i == 0) {
                    sql += "  find_in_set('" + hobbys[i] + "',hobby) ";
                } else {
                    sql += " or find_in_set('" + hobbys[i] + "',hobby) ";
                }
            }
            sql += ") ";
        }
        return sql;
    }

    @Override
    public Integer count(UserModel model) {
        String sql = "select count(*) count from user where  true ";
        sql += getWhereSql(model);
        return JDBC.count(sql);
    }
}
